﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmStaffLeave

    Sub FillStaff()
        Try
            Me.Cursor = Cursors.Default

            Dim cmd As New SqlCommand("select EmpNo,FullNameEn from StaffProfiles", cnn)
            Dim Reader As SqlDataReader

            Me.ListUsers.Items.Clear()
            Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("EmpNo")
                Item.SubItems.Add(Reader.Item("FullNameEn"))
                Me.ListUsers.Items.Add(Item)
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Clear()
        Me.txtName.Clear()
        Me.txtJobTitle.Clear()
        Me.txtBranch.Clear()
        Me.txtVecationEntitlement.Clear()
        Me.txtVacationDaysTaken.Clear()
        Me.txtVacationBalance.Clear()

        Me.DTPFrom.Value = Now
        Me.DTPTo.Value = Now
        Me.txtLeaveReason.Clear()
        Me.txtContactDetailsDuringLeave.Clear()
        Me.GridLeaveHistory.Rows.Clear()
    End Sub

    Private Sub butClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butClear.Click
        Me.DTPFrom.Value = Now
        Me.DTPTo.Value = Now
        Me.txtLeaveReason.Clear()
        Me.txtContactDetailsDuringLeave.Clear()
    End Sub

    Private Sub butClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butClose.Click
        Me.Close()
    End Sub

    Sub FillVacationHistory()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand(" Select * From StaffLeave Where EmpID=" & CInt(Me.ListUsers.SelectedItems(0).Text), cnn)
            Dim Reader As SqlDataReader

            Me.GridLeaveHistory.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            With Me.GridLeaveHistory
                While Reader.Read
                    Me.GridLeaveHistory.Rows.Add({Reader.Item("SNo"), CDate(Reader.Item("TransDate")).ToString("yyyy"), _
                                                  CDate(Reader.Item("PeriodFrom")).ToString("dd-MMM-yyyy"), _
                                                  CDate(Reader.Item("PeriodTo")).ToString("dd-MMM-yyyy"), Reader.Item("NoOfDays"), _
                                                  Reader.Item("LeaveReson"), Reader.Item("ContactDetails"), _
                                                  Reader.Item("SavedBy"), CDate(Reader.Item("TransDate")).ToString("dd-MMM-yyyy")})

                End While
            End With
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub butSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butSave.Click
        Me.ErrProv.Clear()
        If Me.txtName.Text.Trim.Length = 0 Then
            MsgBox("Please select a staff member from the list")

        ElseIf Me.txtNoOfDays.Value = 0 Then
            Me.ErrProv.SetError(Me.txtNoOfDays, "Please fill in all details")
            Me.txtNoOfDays.Focus()

        ElseIf Me.txtLeaveReason.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtLeaveReason, "Please fill in all details")
            Me.txtLeaveReason.Focus()

        ElseIf Me.txtContactDetailsDuringLeave.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtContactDetailsDuringLeave, "Please fill in all details")
            Me.txtContactDetailsDuringLeave.Focus()

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Insert Into StaffLeave " & _
                                          "(EmpID,EmpName,PeriodFrom,PeriodTo,NoOfDays,LeaveReson,ContactDetails,SavedBy) " & _
                                          "Values " & _
                                          "(@EmpID,@EmpName,@PeriodFrom,@PeriodTo,@NoOfDays,@LeaveReson,@ContactDetails,@SavedBy)", cnn)

                cmd.Parameters.AddWithValue("@EmpID", Me.txtEmpNo.Text)
                cmd.Parameters.AddWithValue("@EmpName", Me.txtName.Text)
                cmd.Parameters.AddWithValue("@PeriodFrom", Me.DTPFrom.Value)
                cmd.Parameters.AddWithValue("@PeriodTo", Me.DTPTo.Value)
                cmd.Parameters.AddWithValue("@NoOfDays", Me.txtNoOfDays.Value)
                cmd.Parameters.AddWithValue("@LeaveReson", Me.txtLeaveReason.Text.Trim)
                cmd.Parameters.AddWithValue("@ContactDetails", Me.txtContactDetailsDuringLeave.Text.Trim)
                cmd.Parameters.AddWithValue("@SavedBy", CurrentUser)

                cnn.Open()
                cmd.ExecuteNonQuery()
                cnn.Close()

                MsgBox("Saved successfully!")

                Me.DTPFrom.Value = Now
                Me.DTPTo.Value = Now
                Me.txtLeaveReason.Clear()
                Me.txtContactDetailsDuringLeave.Clear()

                FillVacationBalance()
                FillVacationHistory()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
        If txtName.Text.Trim.Length = 0 Then
            MsgBox("Please enter a valid staff ID!")
            Exit Sub
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim dap As New SqlDataAdapter("Select * From StaffLeave Where EmpID=" & Me.txtEmpNo.Text, cnn)
                Dim das As New DsVacations

                dap.Fill(das, "StaffLeave")

                Dim rpt As New StaffLeaveStatement
                rpt.SetDataSource(das)
                rptViewer.CrystalReportViewer1.ReportSource = rpt
                rptViewer.ShowDialog()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub frmStaffIncidents_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillStaff()

        Me.WindowState = FormWindowState.Maximized
    End Sub

    Sub FillUsers()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select EmpNo,FullNameEn From StaffProfiles", cnn)
            Dim Reader As SqlDataReader

            Me.ListUsers.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("EmpNo")
                Item.SubItems.Add(Reader.Item("FullNameEn"))
                Me.ListUsers.Items.Add(Item)
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try
    End Sub

    Private Sub ListUsers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListUsers.SelectedIndexChanged
        Clear()

        If Me.ListUsers.SelectedItems.Count > 0 Then
            Try
                Me.Cursor = Cursors.WaitCursor
                Dim cmd As New SqlCommand("Select EmpNo,FullNameEn,Position,Branch,VacationPeriod " & _
                                          "From StaffProfiles where EmpNo=" & CInt(Me.ListUsers.SelectedItems(0).Text), cnn)
                Dim Reader As SqlDataReader

                cnn.Open()
                Reader = cmd.ExecuteReader
                While Reader.Read
                    Me.txtEmpNo.Text = Reader.Item("EmpNo")
                    Me.txtName.Text = Reader.Item("FullNameEn")
                    Me.txtJobTitle.Text = Reader.Item("Position")
                    Me.txtBranch.Text = Reader.Item("Branch")
                    Me.txtVecationEntitlement.Text = CInt(Reader.Item("VacationPeriod"))
                End While
                Reader.Close()
                cnn.Close()

                FillVacationBalance()

                FillVacationHistory()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Sub FillVacationBalance()
        Try
            Me.Cursor = Cursors.WaitCursor


            Dim cmd As New SqlCommand("Select IsNull(Sum(NoOfDays),0) From StaffLeave Where EmpID=" & CInt(Me.ListUsers.SelectedItems(0).Text) & _
                              " and Year(TransDate)=Year(GetDate())", cnn)
            cnn.Open()
            Me.txtVacationDaysTaken.Text = CInt(cmd.ExecuteScalar)
            cnn.Close()

            Me.txtVacationBalance.Text = CInt(Me.txtVecationEntitlement.Text) - CInt(Me.txtVacationDaysTaken.Text)

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Calculate()
        Try
            Me.txtNoOfDays.Value = CInt(DateDiff(DateInterval.DayOfYear, Me.DTPFrom.Value, Me.DTPTo.Value)) + 1
        Catch ex As Exception

        End Try
    End Sub

    Private Sub DTPFrom_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DTPFrom.ValueChanged
        Calculate()
    End Sub

    Private Sub DTPTo_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DTPTo.ValueChanged
        Calculate()
    End Sub

    Private Sub btnPrintVacationRequestForm_Click(sender As System.Object, e As System.EventArgs) Handles btnPrintVacationRequestForm.Click
        Me.ErrProv.Clear()
        If Me.txtName.Text.Trim.Length = 0 Then
            MsgBox("Please select a staff member from the list")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim dap As New SqlDataAdapter("Select * From StaffProfiles Where EmpNo=" & Me.txtEmpNo.Text, cnn)
                Dim das As New DsVacations

                dap.Fill(das, "StaffProfiles")

                Dim rpt As New VacationRequestForm
                rpt.SetDataSource(das)

                Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
                Dim crParameterFieldDefinitions As ParameterFieldDefinitions
                Dim crParameterFieldLocation As ParameterFieldDefinition
                Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
                crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

                'VacationDaysTaken PARAMETER
                crParameterFieldLocation = crParameterFieldDefinitions.Item("VacationDaysTaken")
                crParameterValues = crParameterFieldLocation.CurrentValues
                crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                crParameterDiscreteValue.Value = Me.txtVacationDaysTaken.Text
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

                'VacationBalance PARAMETER
                crParameterFieldLocation = crParameterFieldDefinitions.Item("VacationBalance")
                crParameterValues = crParameterFieldLocation.CurrentValues
                crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                crParameterDiscreteValue.Value = Me.txtVacationBalance.Text
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

                rptViewer.CrystalReportViewer1.ReportSource = rpt
                rptViewer.ShowDialog()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub
End Class